<?php
class Shop_Model_SeoParamForRail extends Shop_Model_Base{

	/**
	 * 表示用変換
	 * @param $cityList
	 * @param $params
	 * @return 変換後リスト
	 */
	public function convertData($cityList, $params)
	{
		return null;
	}

	/**
	 * sql文生成
	 * @param $params
	 * @param $db_params
	 * @return sql文
	 */
	protected function generateSql($params, &$db_params)
	{
		// 沿線CDが設定されていない場合→終了
		if (empty($params['RailCd']) && empty($params['StCd'])) return array();
		
		$strQryWhere = '';
		$strQryEnsen = '';
		
		$queryList = array();
		
		if (!empty($params['RailCd']))
		{
			foreach ($params['RailCd'] as $key => $railCd) {
				if (strlen($railCd) == 4) {
					$queryList[] = "(M02_ENSEN_CD = :railCd{$key})";
					$db_params['railCd'.$key] = $railCd;
				} else {
					$queryList[] = "(M43_TODOUFUKEN_CD = :prefCd{$key} AND M02_ENSEN_CD = :railCd{$key})";
					$db_params['prefCd'.$key] = substr($railCd, 0, 2);
					$db_params['railCd'.$key] = substr($railCd, 2, 4);
				}
			}
			
			$strQryWhere = implode(' OR ', $queryList);
			$strQryEnsen = <<< DOC_END
					SELECT M02_ENSEN_CD,M02_ENSEN_NAME
					 FROM M02ENSEN
					WHERE {$strQryWhere}
DOC_END;
			
		}
		if (!empty($params['StCd']))
		{
			foreach ($params['StCd'] as $key => $StCd) {
					$queryList[] = "(M03_EKI_CD = :stCd{$key})";
					$db_params['StCd'.$key] = substr($StCd, 2, 7);
			}
			
			$strQryWhere = implode(' OR ', $queryList);
			$strQryEnsen = <<< DOC_END
					SELECT M02_ENSEN_CD,M02_ENSEN_NAME
					 FROM M02ENSEN 
					WHERE M02_ENSEN_CD 
							IN (SELECT M03_ENSEN_CD FROM M03EKI 
									WHERE {$strQryWhere} )
DOC_END;
			
		
		}
		
		
$sql = <<< DOC_END
	SELECT
		  M43_TODOUFUKEN_CD as TODOUFUKEN_CD
		, M02_ENSEN_CD as LIST_CD
		, M43_TODOUFUKEN
		, '' as TODOUFUKEN
		, M02_ENSEN_NAME as LIST
	FROM
		  ({$strQryEnsen})
		, M04ENSEN_AREA
		, M43TODOUFUKEN
	WHERE
		M02_ENSEN_CD = M04_ENSEN_CD
		AND M04_TODOUFUKEN_CD = M43_TODOUFUKEN_CD
		--AND ({$strQryWhere})
	ORDER BY
		  M43_TODOUFUKEN_CD
		, M02_ENSEN_CD
DOC_END;
		
		return $sql;
	}
}
?>